<?php

/**
 * 单独网点excel文件处理
 * User: Pulingao
 * Date: 2014/12/30
 */
class NetworkExcel extends Excel {

	/**
	 * 导出网点基本数据
	 * @param type $tableData	问卷表基本数据
	 * @param type $fileName	导出文件名称
	 * @param type $type		导出文件类型，xls or xlsx
	 * @return type
	 */
	public function networkExport($tableData, $fileName = 'network_export', $type = 'xls') {
		if (empty($tableData)) {
			return array();
		}

		$fillData = array();
		foreach ($tableData as $key => $data) {
			$fillData[$key][] = $data['network_id'];
			$fillData[$key][] = $data['network_num'];
			$fillData[$key][] = $data['network_name'];
			$fillData[$key][] = $data['network_username'];
			$fillData[$key][] = (string)$data['network_pwd'];
			$fillData[$key][] = $data['bank_name'] ? $data['bank_name'] : '--';
			$fillData[$key][] = $data['level_name'];
			$fillData[$key][] = $data['auth_grade_name'];
			$fillData[$key][] = $data['up_network_num'];
			$fillData[$key][] = $data['up_network_name'];
			$fillData[$key][] = $data['create_time'] ? date('Y-m-d H:i:s', $data['create_time']) : '';
			$fillData[$key][] = $data['update_time'] ? date('Y-m-d H:i:s', $data['update_time']) : '';
			$fillData[$key][] = $data['user_name'];
		}
		# 设置导出基本信息
		$this->FileName = $fileName;
		$this->Type = $type;
		$this->exportHeader();

		# 设置excel头部内容
		$excelTitles = array('网点ID', '网点编号', '网点名称', '网点登陆昵称', '网点登陆密码', '所属银行', '网点等级', '权限等级', '上级网点编号', '上级网点名称', '创建时间', '更新时间', '操作人');
		foreach ($excelTitles as $k => $v) {
                        if ($k == 4) {
                            $this->PHPExcelExport->setActiveSheetIndex(0)->setCellValueExplicit('E1', $v,PHPExcel_Cell_DataType::TYPE_STRING2);
                            $this->PHPExcelExport->getActiveSheet()->getStyle('E1')->getNumberFormat()->setFormatCode("@");
                        } else {
                            $this->PHPExcelExport->setActiveSheetIndex(0)->setCellValueByColumnAndRow($k, 1, $v);
                        }
			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)
					->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFC000');
			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)
					->getFont()->setBold(true);
		}

		$x = 2;
		foreach ($fillData as $value) {
			$y = 0;
			foreach ($value as $k => $v) {
                            if ($y == 4) {
                                $this->PHPExcelExport->setActiveSheetIndex(0)->setCellValueExplicit('E'.$x, $v,PHPExcel_Cell_DataType::TYPE_STRING2);
                                $this->PHPExcelExport->getActiveSheet()->getStyle('E'.$x)->getNumberFormat()->setFormatCode("@");
                            } else {
                                $this->PHPExcelExport->setActiveSheetIndex(0)->setCellValueByColumnAndRow($y, $x, $v);
                            }
				
				// 让对应的excel表格自动换行
//				$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getAlignment()->setWrapText(true);
//				$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getFont()->setSize(10);
				// 垂直居中
				$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//				$this->PHPExcelExport->getActiveSheet()->getRowDimension($x)->setRowHeight(15);
				$this->PHPExcelExport->getActiveSheet()->getColumnDimensionByColumn($y)->setWidth(20);
				$y++;
			}
			$x++;
		}

		$this->exportFooter();
		exit;
	}

	/**
	 * 根据问卷内容，生成问卷回答模板，提供下载
	 * @param type $questionId	问卷ID
	 * @param type $tableData	问卷内容基本数据
	 * @param type $fileName	导出文件名称
	 * @param type $type		导出文件类型，xls or xlsx
	 * @return type
	 */
	public function quesionAnswerTemplate($questionId, $tableData, $fileName = '', $type = 'xlsx') {
		if (empty($tableData) || !$questionId) {
			return array();
		}
        if ($questionId == 875 || $questionId == 870) {
           // print_r($tableData);exit;
        }
		sort($tableData);
		# 解析时间的问题
		$dateTimeField = array(
			'日期' => '2015-04-05（excel中此列单元格请设置为文本格式）',
			'时间' => '09:18:43（excel中此列单元格请设置为文本格式）',
		);
		$baseField = array(
			'网点编号' => '填写网点编号的值',
			'网点名称' => '填写网点名称的值，此处的网点名称需要和同一行的网点编号一一对应',
		);



		//print_r($tableData);exit;
		$fillData = array();
		foreach ($tableData as $key => $data) {
			if ($data['is_basic']) {
				//检查日期和网点时间生成
				$dataTime = false;
				foreach ($dateTimeField as $dtKey => $value) {
					if (false !== strstr($data['title'], $dtKey)) {
						$excelTitles[] = array(
							'title' => $data['title'],
							'value' => $value
						);
						$dataTime = true;
					}
				}
				if ($dataTime) {
					continue;
				}
				
				// 网点编号和网点名称
				if (in_array($data['title'], array_keys($baseField))) {
					$excelTitles[] = array(
						'title' => $data['title'],
						'value' => $baseField[$data['title']]
					);
					continue;
				}
				
				$excelTitles[] = array(
					'title' => $data['title'],
					'value' => '【此行 为填写回答内容的提示信息，正式填写回答内容时需删除此行】'
				);
				continue;
			}

			// 非基础信息
			$jArr = array(
				0 => array(
					'title' => '得分',
					'value' => '请填写指标的得分，如果没有，则留空'
				),
				1 => array(
					'title' => '选项值',
					'value' => '请填写指标的选项，如果没有，则留空'
				),
				2 => array(
					'title' => '情景描述',
					'value' => '请填写指标的回答内容，如果没有，则留空'
				),
			);
			for($j=0;$j<3;$j++){
				$excelTitles[] = array(
					'title' => $jArr[$j]['title'] ? ($data['question_num'].$jArr[$j]['title']) : $data['question_num'],
					'value' => $jArr[$j]['value']
				);
			}
		}
		//print_r($excelTitles);exit;
		# 组织表头和内容填写提示数据
		$newExcelTitles = array();
		foreach ($excelTitles as $key => $val) {
			$fillData[0][$key] = $val['value'];
			unset($excelTitles[$key]['value']);
			$newExcelTitles[$key] = $val['title'];
		}
		
		# 设置导出基本信息
		$this->FileName = $fileName ? $fileName : '问卷回答导入模板';
		$this->Type = $type;
		$this->exportHeader();
		//print_r($newExcelTitles);exit;
		# 根据问卷的所有网点，生成填充数据
		$qData = QuestionBase::model()->find('question_id=:question_id', array(':question_id' => $questionId));
		$questionNetwork = NetworkQuestion::model()->getQuestionNetwork($questionId, $qData->project_id);
		//print_r($questionNetwork);exit;
		foreach ($newExcelTitles as $key => $val) {
			foreach ($questionNetwork as $nkey => $nd) {
				// 默认不生成总行和竞争网点的内容
				if ($nd['level'] == NetWork::NETWORK_LEVEL_FIVE || $nd['level'] == Network::NETWORK_LEVEL_ONE) {
					continue;
				}
				
				$fillData[$nd['network_id']][$key] = '';
				// 网点编号和网点名称如果存在于问卷的基础信息中，则从网点中区别对应出来
				if (in_array($val, array_keys($baseField))) {
					if ($val == '网点编号') {
						$fillData[$nd['network_id']][$key] = $nd['network_num'];
					}else{
						$fillData[$nd['network_id']][$key] = $nd['network_name'];
					}	
				}
				
			}
		}
		//print_r($fillData);exit;
		# 设置excel头部内容
		$this->PHPExcelExport->setActiveSheetIndex(0);
		foreach ($newExcelTitles as $k => $v) {
			$this->PHPExcelExport->getActiveSheet()->setCellValueByColumnAndRow($k, 1, $v);
			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			
			//设置标题样式
//			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)
//					->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFC000');
//			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)
//					->getFont()->setBold(true);
		}

		$x = 2;
		foreach ($fillData as $value) {
			$y = 0;
			foreach ($value as $k => $v) {
				$this->PHPExcelExport->getActiveSheet()->setCellValueByColumnAndRow($y, $x, $v);
				// 让对应的excel表格自动换行
//				$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getAlignment()->setWrapText(true);
//				$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getFont()->setSize(10);
				// 垂直居中
				$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//				$this->PHPExcelExport->getActiveSheet()->getRowDimension($x)->setRowHeight(15);
//				$this->PHPExcelExport->getActiveSheet()->getColumnDimensionByColumn($y)->setWidth(20);
				$y++;
			}
			$x++;
		}
		
		$this->exportFooter();
		exit;
	}
	
	/**
	 * 生成问卷对标回答数据模板，提供下载
	 * @param type $questionId	问卷ID
	 * @param type $tableData	问卷内容基本数据
	 * @param type $fileName	导出文件名称
	 * @param type $type		导出文件类型，xls or xlsx
	 * @return type
	 */
	public function quesionBenchmarkTemplate($questionId, $tableData, $fileName = '', $type = 'xlsx') {
		if (empty($tableData) || !$questionId) {
			return array();
		}
		$someItems = $tableData['some_items'];
		unset($tableData['some_items']);
		
		# 组织表头和内容填写提示数据
		$excelTitle = array(
			'network_name' => '网点名称',
			'network_num' => '网点编号',
			'question_num' => '指标编码',
			'standard_score' => '标准分值',
			'answer_score' => '实际得分',
			'answer_desc' => '检查描述',
			'suggestion' => '还分申请描述',
			'level_one_suggestion' => '总行建议',
			'level_one_last_time' => '总行最后提交时间',
			'sino_handle_suggestion' => '正达银友复核情况      （正达银友处理意见）',
			'sino_handle_isreturn' => '是否还分        （正达银友处理结果）',
			'sino_handle_isreturnvalue' => '还分值',
		);
		$mustThField = array();
		foreach ($someItems as $net => $val) {
			$tmp = array();
			switch ($net) {
				case 2:
					$tmp = array(
						'level_two_suggestion' => '分行反馈意见            （分行处理意见）',
						'level_two_isreturn' => '是否还分        （分行处理结果）',
						'level_two_dissent' => '分行提交状态',
					);
					break;
				case 3:
					$tmp = array(
						'level_three_suggestion' => '支行反馈意见            （支行处理意见）',
						'level_three_isreturn' => '是否还分        （支行处理结果）',
						'level_three_dissent' => '支行提交状态',
					);
					break;
				default:
					break;
			}
			$mustThField = array_merge($mustThField, $tmp);
		}
		# 合并表头
		$excelTitle = array_merge($excelTitle, $mustThField, array('export_time' => '后台导出时间'));
		
		$fillData = array();
		foreach ($excelTitle as $exKey => $exValue) {
			foreach ($tableData as $key => $chrD) {
				$fillData[$key][] = $chrD[$exKey];
			}
		}
		
		# 设置导出基本信息
		$this->FileName = $fileName ? $fileName : '问卷申诉导出模板';
		$this->Type = $type;
		$this->exportHeader();
		
		# 设置excel头部内容
		$this->PHPExcelExport->setActiveSheetIndex(0);
		$k = 0;
		foreach ($excelTitle as $v) {
			$this->PHPExcelExport->getActiveSheet()->setCellValueByColumnAndRow($k, 1, $v);
			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			
			//设置标题样式
//			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)
//					->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFC000');
//			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)
//					->getFont()->setBold(true);
			$k++;
		}

		$x = 2;
		foreach ($fillData as $value) {
			$y = 0;
			foreach ($value as $k => $v) {
				$this->PHPExcelExport->getActiveSheet()->setCellValueByColumnAndRow($y, $x, $v);
				// 让对应的excel表格自动换行
				if (!in_array($y, array(0,3,5))) {
					$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getAlignment()->setWrapText(true);
				}
				$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getFont()->setSize(10);
				// 垂直居中
				$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
				//$this->PHPExcelExport->getActiveSheet()->getRowDimension($x)->setRowHeight(15);
				if (!in_array($y, array(1,2,3,4))) {
					$this->PHPExcelExport->getActiveSheet()->getColumnDimensionByColumn($y)->setWidth(20);
				}
				$y++;
			}
			$x++;
		}
		
		$this->exportFooter();
		exit;
	}
	
	
	/**
	 * 网点报备信息导出
	 * @param type $project_id
	 * @param type $tableData
	 * @param type $fileName
	 * @param type $type
	 * @return type
	 */
	public function reportExport($project_id, $tableData, $fileName = '', $type = 'xlsx') {
		if (empty($tableData) || !$project_id) {
			return array();
		}
		
		# 组织表头和内容填写提示数据
		$excelTitle = array(
			'number' => '序号',
			'network_num' => '网点编号',
			'network_name' => '网点名称',
                        'nr_order' => '报备序号',
			'project_name' => '所属项目',
			'content' => '报备内容',
			'start_time' => '开始时间',
			'end_time' => '结束时间',
			'self_status' => '当前报备状态',
			'one_status' => '总行提交状态',
			'two_status' => '分行提交状态',
			'three_status' => '支行提交状态',
			'create_time' => '报备提交时间',
		);
		$fillData = array();
		foreach ($excelTitle as $exKey => $exValue) {
			foreach ($tableData as $key => $chrD) {
				$fillData[$key][] = $chrD[$exKey] ? $chrD[$exKey] : '--';
			}
		}
		
		# 设置导出基本信息
		$this->FileName = $fileName ? $fileName : '问卷申诉导出模板';
		$this->Type = $type;
		$this->exportHeader();
		
		# 设置excel头部内容
		$this->PHPExcelExport->setActiveSheetIndex(0);
		$k = 0;
		foreach ($excelTitle as $v) {
			$this->PHPExcelExport->getActiveSheet()->setCellValueByColumnAndRow($k, 1, $v);
			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			
			//设置标题样式
//			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)
//					->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFC000');
//			$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($k, 1)
//					->getFont()->setBold(true);
			$k++;
		}

		$x = 2;
		foreach ($fillData as $value) {
			$y = 0;
			foreach ($value as $k => $v) {
				$this->PHPExcelExport->getActiveSheet()->setCellValueByColumnAndRow($y, $x, $v);
				// 让对应的excel表格自动换行
//				if (!in_array($y, array(4))) {
//					$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getAlignment()->setWrapText(true);
//				}
				$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getFont()->setSize(10);
				// 垂直居中
				//$this->PHPExcelExport->getActiveSheet()->getStyleByColumnAndRow($y, $x)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
				$y++;
			}
			$x++;
		}
		
		$this->exportFooter();
		exit;
		
	}

}
